package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.ysd.entity.ChuangShiRen;
import com.ysd.util.DBManager;

public class ChuangShiRenDaoimpl {
	/**
	 * 查询创始人的数据分页
	 * @return
	 */
	public ArrayList<ChuangShiRen> getChuagShiRen(int page, int size){
		Connection conn=DBManager.getConnection();
		ArrayList<ChuangShiRen> list=new ArrayList<ChuangShiRen>();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			String sql="SELECT * FROM chuangshiren LIMIT "+(page-1)*size+","+size;
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				ChuangShiRen csr=new ChuangShiRen(rs.getInt("bossId"), rs.getString("bossName"), rs.getString("zhiWei"), rs.getString("touXiang"), rs.getString("sex"), rs.getInt("age"));
				list.add(csr);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 获得所有设计师
	 * @return
	 */
	public ArrayList<ChuangShiRen> getChuagShiRen(){
		Connection conn=DBManager.getConnection();
		ArrayList<ChuangShiRen> list=new ArrayList<ChuangShiRen>();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			String sql="SELECT * FROM chuangshiren";
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				ChuangShiRen csr=new ChuangShiRen(rs.getInt("bossId"), rs.getString("bossName"), rs.getString("zhiWei"), rs.getString("touXiang"), rs.getString("sex"), rs.getInt("age"));
				list.add(csr);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	/**
	 * 根据id查询设计师的长度
	 * @param page
	 * @param size
	 * @return
	 */
	public ArrayList<ChuangShiRen> getChuagShiRensize(){
		Connection conn=DBManager.getConnection();
		ArrayList<ChuangShiRen> list=new ArrayList<ChuangShiRen>();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			String sql="SELECT bossId FROM chuangshiren ";
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				ChuangShiRen csr=new ChuangShiRen(rs.getInt("bossId"));
				list.add(csr);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	

	
	
	/**
	 * 根据id删除一条信息
	 * @param id
	 * @return
	 */
	public int delShiJiShi(int id) {
		return DBManager.executeUpdate("delete from chuangshiren where bossId="+id);
	}
	
	/**
	 * 添加一个创始人
	 * @param ChuangShiRen
	 * @return
	 */
	public int addSheJiShi(ChuangShiRen ChuangShiRen) {
		return DBManager.executeUpdate("INSERT into chuangshiren VALUES(null,'"+ChuangShiRen.getBossName()+"','"+ChuangShiRen.getZhiWei()+"','"+ChuangShiRen.getTouXiang()+"','"+ChuangShiRen.getSex()+"',"+ChuangShiRen.getAge()+")");
	}
	
	
	/**
	 * 查询所有数据
	 * @param c
	 * @param page
	 * @param size
	 * @return
	 */
	public ArrayList<ChuangShiRen> selSheJiShiWhere(ChuangShiRen c,int page,int size){
		ArrayList<ChuangShiRen> list=new ArrayList<ChuangShiRen>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			String sql="select * from chuangshiren where 1=1";
			if(c.getBossName()!=null&&!c.getBossName().equals("")) {
				sql+=" and bossname LIKE  '%"+c.getBossName()+"%'";
			}
			
			if(c.getZhiWei()!=null&&!c.getZhiWei().equals("")) {
				sql+=" and bossname LIKE  '%"+c.getZhiWei()+"%'";
			}
			
			
			if(c.getSex()!=null&&!c.getSex().equals("")) {
				sql+=" and sex='"+c.getSex()+"'";
			}
			sql+=" ORDER BY bossId limit "+(page-1)*size+","+size;
			System.out.println(sql);
			 rs=st.executeQuery(sql);
			 while(rs.next()) {
				 ChuangShiRen csr=new ChuangShiRen(rs.getInt("bossId"), rs.getString("bossName"), rs.getString("zhiWei"), rs.getString("touXiang"), rs.getString("sex"), rs.getInt("age"));
				 list.add(csr);
			 }
			 return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	/**
	 * 多条件查询长度
	 * @param c
	 * @return
	 */
	public ArrayList<ChuangShiRen> selSheJiShiWherepage(ChuangShiRen c){
		ArrayList<ChuangShiRen> list=new ArrayList<ChuangShiRen>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			String sql="select * from chuangshiren where 1=1";
			if(c.getBossName()!=null&&!c.getBossName().equals("")) {
				sql+=" and bossname LIKE  '%"+c.getBossName()+"%'";
			}
			
			if(c.getZhiWei()!=null&&!c.getZhiWei().equals("")) {
				sql+=" and bossname LIKE  '%"+c.getZhiWei()+"%'";
			}
			
			
			if(c.getSex()!=null&&!c.getSex().equals("")) {
				sql+=" and sex='"+c.getSex()+"'";
			}
			
			System.out.println(sql);
			 rs=st.executeQuery(sql);
			 while(rs.next()) {
				 ChuangShiRen csr=new ChuangShiRen(rs.getInt("bossId"), rs.getString("bossName"), rs.getString("zhiWei"), rs.getString("touXiang"), rs.getString("sex"), rs.getInt("age"));
				 list.add(csr);
			 }
			 return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	/**
	 * 通过设计师的姓名查询数据
	 * @param name
	 * @return
	 */
	public ArrayList<ChuangShiRen> selSheJiShi(String name){
		ArrayList<ChuangShiRen> list=new ArrayList<ChuangShiRen>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			 st=conn.createStatement();
			 rs=st.executeQuery("SELECT * FROM chuangshiren where bossname='"+name+"'");
			while(rs.next()) {
				int bossId=rs.getInt("bossId");
				String bossName=rs.getString("bossName");
				String zhiWei=rs.getString("zhiWei");
				String touXiang=rs.getString("touXiang");
				String sex=rs.getString("sex");
				int age=rs.getInt("age");
				ChuangShiRen c=new ChuangShiRen(bossId, bossName, zhiWei, touXiang, sex, age);
				list.add(c);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	
	
	
	public ChuangShiRen getChuagShiRenById(int id){
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		ChuangShiRen csr=null;
		try {
			 st=conn.createStatement();
			String sql="SELECT * FROM chuangshiren where bossId="+id;
			 rs=st.executeQuery(sql);
			while(rs.next()) {
				 csr=new ChuangShiRen(rs.getInt("bossId"), rs.getString("bossName"), rs.getString("zhiWei"), rs.getString("touXiang"), rs.getString("sex"), rs.getInt("age"));
				
			}
			return csr;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	
	public int editSheJiShiById(ChuangShiRen c) {
		return DBManager.executeUpdate("UPDATE chuangshiren set bossName='"+c.getBossName()+"',zhiWei='"+c.getZhiWei()+"',touXiang='"+c.getTouXiang()+"',sex='"+c.getSex()+"',age='"+c.getAge()+"' WHERE bossId="+c.getBossId());
	}
}
